package com.walker.service.catalogue;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.walker.util.CommonUtils;

@Service
public class CatalogueHomeService {

	@Autowired
	@Qualifier("dmJdbcTemplate")
	private JdbcTemplate dmJdbcTemplate;
	
	/**
	 * 全局搜索
	 * @param keyword
	 * @return
	 */
	public Map<String,Object> getGobalSerachList(String keyword){
		
		Map<String,Object> data = new HashMap<String,Object>();
		data.put("cube_info", getCubeSerachList(keyword));
		data.put("derivate_info", getDerivateSerachList(keyword));
		data.put("field_info", getFieldSerachList(keyword));
		data.put("datasource_info", getDataSourceSerachList(keyword));
		data.put("service_info", getServicesSerachList(keyword));
		data.put("project_info", getProjectSerachList(keyword));
		data.put("func_info", getFuncSerachList(keyword));

		return data;
	}
	
	/**
	 * 数据板块
	 * @return
	 */
	public List<Map<String, Object>> listBusiness() {
		String sql = "select busin_id,busin_name,comments,busin_code,icon_code,DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%s') as create_time,(select count(1) from t_meta_data_field b where b.busin_id = a.busin_id) as data_field_count from t_meta_business_field a order by create_time desc";
		
		List<Map<String,Object>> list = dmJdbcTemplate.queryForList(sql);
		for(Map<String,Object> map : list){
			sql = "select data_name from t_meta_data_field where busin_id = ?";
			map.put("data_field_list", dmJdbcTemplate.queryForList(sql,map.get("busin_id")));
		}
		return list;
	}
	
	/**
	 * 数据域
	 * @param busin_id
	 * @return
	 */
	public List<Map<String,Object>> getDataFieldByBusinId(String busin_id){
		String sql = "select data_name from t_meta_data_field where busin_id = ?";
		return dmJdbcTemplate.queryForList(sql,busin_id);
	}
	
	/**
	 * 欢迎信息
	 * @param login_code
	 * @return
	 */
	public String getWelcomeInfo(String login_code){
		SimpleDateFormat sdf = new SimpleDateFormat("HH");
		
		int hh = Integer.parseInt(sdf.format(new Date()));
		
		String info = "Hi，";
		if(StringUtils.isNotBlank(CommonUtils.valueOf(login_code))){
			info += login_code+"，";
		}
		if(hh >= 0 && hh <= 6){
			info += "凌晨好！";
		}else if(hh > 6 && hh<= 12){
			info += "上午好！";
		}else if(hh > 12 && hh <= 13){
			info += "中午好！";
		}else if(hh > 13 && hh <= 18){
			info += "下午好！";
		}else if(hh > 18 && hh <= 24){
			info += "晚上好！";
		}
		return info;
	}
	
	/**
	 * 最近浏览
	 * @param userName
	 * @return
	 */
	public List<Map<String, Object>> listTrail(String login_code) {
		StringBuffer sql = new StringBuffer();
		sql.append("select id,");
		sql.append("       case when object_type in('37','38','39','40','42','43') then '42' else object_type end as object_type,");
		sql.append("       project_code,");
		sql.append("       concat_ws('.',project_code,object_code) as object_code,");
		sql.append("       object_name,");
		sql.append("       cast(is_collect as UNSIGNED INTEGER) as is_collect,");
		sql.append("       source_type,");
		sql.append("       id as cube_id");
		sql.append("  from t_asset_m_trail");
		sql.append(" where login_code = ?");
		sql.append(" order by trail_date desc limit 10");
		
		return dmJdbcTemplate.queryForList(sql.toString(), new Object[] { login_code });
	}

	/**
	 *最近收藏 
	 * @param params
	 * @param login_code
	 * @return
	 */
	public Map<String, Object> listCollect(Map<String,Object> params,String login_code) {
		int pageSize = Integer.parseInt(CommonUtils.valueOf(params.get("pageSize")));
		int pageNo = Integer.parseInt(CommonUtils.valueOf(params.get("pageNo")));

		StringBuffer sql = new StringBuffer();
		sql.append("select case when object_type in('37','38','39','40','42','43') then '42' else object_type end as object_type,");
		sql.append("       project_code,");
		sql.append("       concat_ws('.',project_code,object_code) as object_code,");
		sql.append("       object_name,");
		sql.append("       source_type,");
		sql.append("       id as cube_id");
		sql.append("  from t_asset_m_trail");
		sql.append(" where login_code = ?");
		sql.append("   and is_collect = 1");
		sql.append(" order by trail_date desc limit ? offset ?");
		
		List<Object> param = new ArrayList<Object>();
		param.add(login_code);
		param.add(pageSize);
		param.add((pageNo - 1) * pageSize);
		
		List<Map<String,Object>> list = dmJdbcTemplate.queryForList(sql.toString(), param.toArray());
		
		String sqlCount = "select count(1) as count from t_asset_m_trail where login_code = ? and is_collect = 1";
		Object count = dmJdbcTemplate.queryForMap(sqlCount,login_code).get("count");
		
		Map<String,Object> data = new HashMap<String,Object>();
		data.put("data", list);
		data.put("totalRecords", count);
		
		return data;
	}
	
	/**
	 * 最近使用
	 * @param login_code
	 * @return
	 */
	public List<Map<String, Object>> listHot(String login_code) {
		StringBuffer sql = new StringBuffer();
		sql.append("select id,");
		sql.append("      case when object_type in('37','38','39','40','42','43') then '42' else object_type end as object_type,");
		sql.append("       project_code,");
		sql.append("      concat_ws('.',project_code,object_code) as object_code,");
		sql.append("      object_name,");
		sql.append("      id as cube_id,");
		sql.append("      source_type,");
		sql.append("      cast(is_collect as UNSIGNED INTEGER) is_collect");
		sql.append("  from t_asset_m_trail");
		sql.append(" where login_code = ?");
		sql.append(" order by hot_value desc limit 5");
		
		return dmJdbcTemplate.queryForList(sql.toString(), new Object[] { login_code });
	}
	
	/*private Map<String,Object> getCubeSerachList(String keyword){
		List<Object> paramList = new ArrayList<Object>();
		
		String countSql = "select count(1) count from t_meta_cubes cubes,t_meta_sync_maxc_cubes sync where cubes.project_id = sync.project_id and cubes.cube_code = sync.cube_code and object_type in('10','11','16','17','18','50') ";
		if(StringUtils.isNotBlank(keyword)){
			countSql += " and (sync.cube_code like ? or sync.cube_name like ?)";
			paramList.add("%"+keyword+"%");
			paramList.add("%"+keyword+"%");
		}
		Object totalRecords = dmJdbcTemplate.queryForMap(countSql,paramList.toArray()).get("count");
		
		StringBuffer sql = new StringBuffer();
		sql.append("select cubes.cube_id as id,concat(sync.cube_code, '(', sync.cube_name, ')') as match_content,");
		sql.append("       param.param_name as match_name,");
		sql.append("       1 as match_type");
		sql.append("  from t_meta_cubes cubes inner join t_meta_sync_maxc_cubes sync on cubes.project_id = sync.project_id and cubes.cube_code = sync.cube_code");
		sql.append("  left join t_meta_public_param param");
		sql.append("    on param.param_value = cubes.object_type");
		sql.append("   and param.param_key = 'object_type'");
		sql.append("  where object_type in('10','11','16','17','18','50')");
		if(StringUtils.isNotBlank(keyword)){
			sql.append(" and (sync.cube_code like ? or sync.cube_name like ?)");
		}
		sql.append(" order by create_time desc limit 10");
		List<Map<String,Object>> cubeList = dmJdbcTemplate.queryForList(sql.toString(),paramList.toArray());
		
		Map<String,Object> data = new HashMap<String,Object>();
		data.put("cubes_desc","数据表("+totalRecords+")");
		data.put("cubes_data", cubeList);
		
		return data;
	}*/
	
	private Map<String,Object> getCubeSerachList(String keyword){
		List<Object> paramList = new ArrayList<Object>();
		
		String countSql = "select count(1) count from t_meta_cubes where object_type in('10', '11', '16', '17', '18', '50')";
		if(StringUtils.isNotBlank(keyword)){
			countSql += " and (cube_code like ? or cube_name like ?)";
			paramList.add("%"+keyword+"%");
			paramList.add("%"+keyword+"%");
		}
		Object totalRecords = dmJdbcTemplate.queryForMap(countSql,paramList.toArray()).get("count");
		
		StringBuffer sql = new StringBuffer();
		sql.append("select cubes.cube_id as id,concat(cube_code, '(', cube_name, ')') as match_content,");
		//sql.append("       param.param_name as match_name,");
		sql.append("       project_code,");
		sql.append("       'hive' as source_type,");
		sql.append("       1 as match_type");
		sql.append("  from t_meta_cubes cubes");
		sql.append("  left join t_meta_public_param param");
		sql.append("    on param.param_value = cubes.object_type");
		sql.append("   and param.param_key = 'object_type'");
		sql.append("  where object_type in('10', '11', '16', '17', '18', '50')");
		if(StringUtils.isNotBlank(keyword)){
			sql.append(" and (cube_code like ? or cube_name like ?)");
		}
		sql.append(" order by create_time desc limit 10");
		List<Map<String,Object>> cubeList = dmJdbcTemplate.queryForList(sql.toString(),paramList.toArray());
		
		Map<String,Object> data = new HashMap<String,Object>();
		data.put("cubes_desc","数据表("+totalRecords+")");
		data.put("cubes_data", cubeList);
		
		return data;
	}
	
	private Map<String,Object> getDerivateSerachList(String keyword){
		List<Object> paramList = new ArrayList<Object>();
		
		String countSql = "select count(1) count from t_meta_derivative_index where 1 = 1";
		if(StringUtils.isNotBlank(keyword)){
			countSql += " and (derivative_code like ? or derivative_name like ?)";
			paramList.add("%"+keyword+"%");
			paramList.add("%"+keyword+"%");
		}
		Object totalRecords = dmJdbcTemplate.queryForMap(countSql,paramList.toArray()).get("count");
		
		StringBuffer sql = new StringBuffer();
		sql.append("select id,concat(derivative_code, '(', derivative_name, ')') as match_content,");
		//sql.append("       '指标' as match_name,");
		sql.append("       2 as match_type");
		sql.append("  from t_meta_derivative_index");
		sql.append(" where 1 = 1");
		if(StringUtils.isNotBlank(keyword)){
			sql.append(" and (derivative_code like ? or derivative_name like ?)");
		}
		sql.append(" order by oper_time desc limit 10");
		List<Map<String,Object>> derivateList = dmJdbcTemplate.queryForList(sql.toString(),paramList.toArray());
		
		Map<String,Object> data = new HashMap<String,Object>();
		data.put("derivate_desc","指标("+totalRecords+")");
		data.put("derivate_data", derivateList);
		
		return data;
	}
	
	/*private Map<String,Object> getFieldSerachList(String keyword){
		List<Object> paramList = new ArrayList<Object>();
		
		String countSql = "select count(1) count from t_meta_element ele inner join t_meta_sync_maxc_element sync on ele.element_code = sync.element_code and ele.element_type = sync.element_type";
		if(StringUtils.isNotBlank(keyword)){
			countSql += " and (sync.element_code like ? or sync.element_name like ?)";
			paramList.add("%"+keyword+"%");
			paramList.add("%"+keyword+"%");
		}
		Object totalRecords = dmJdbcTemplate.queryForMap(countSql,paramList.toArray()).get("count");
		
		StringBuffer sql = new StringBuffer();
		sql.append("select element_id as id,concat(sync.element_code,'(',sync.element_name,')') as match_content,sync.element_code,");
		sql.append("       '字段' as match_name,");
		sql.append("       3 as match_type");
		sql.append("  from t_meta_element ele inner join t_meta_sync_maxc_element sync on ele.element_code = sync.element_code and ele.element_type = sync.element_type");
		if(StringUtils.isNotBlank(keyword)){
			sql.append(" and (sync.element_code like ? or sync.element_name like ?)");
		}
		sql.append(" order by oper_time desc limit 10");
		List<Map<String,Object>> fieldList = dmJdbcTemplate.queryForList(sql.toString(),paramList.toArray());
		
		Map<String,Object> data = new HashMap<String,Object>();
		data.put("field_desc","字段("+totalRecords+")");
		data.put("field_data", fieldList);
		
		return data;
	}*/
	
	private Map<String,Object> getFieldSerachList(String keyword){
		List<Object> paramList = new ArrayList<Object>();
		
		String countSql = "select count(1) count from t_meta_element where 1 = 1";
		if(StringUtils.isNotBlank(keyword)){
			countSql += " and (element_code like ? or element_name like ?)";
			paramList.add("%"+keyword+"%");
			paramList.add("%"+keyword+"%");
		}
		Object totalRecords = dmJdbcTemplate.queryForMap(countSql,paramList.toArray()).get("count");
		
		StringBuffer sql = new StringBuffer();
		sql.append("select element_id as id,concat(element_code,'(',element_name,')') as match_content,element_code,");
		//sql.append("       '字段' as match_name,");
		sql.append("       3 as match_type");
		sql.append("  from t_meta_element");
		sql.append(" where 1 = 1");
		if(StringUtils.isNotBlank(keyword)){
			sql.append(" and (element_code like ? or element_name like ?)");
		}
		sql.append(" order by oper_time desc limit 10");
		List<Map<String,Object>> fieldList = dmJdbcTemplate.queryForList(sql.toString(),paramList.toArray());
		
		Map<String,Object> data = new HashMap<String,Object>();
		data.put("field_desc","字段("+totalRecords+")");
		data.put("field_data", fieldList);
		
		return data;
	}
	
	private Map<String,Object> getDataSourceSerachList(String keyword){
		List<Object> paramList = new ArrayList<Object>();
		
		String countSql = "select count(1) count from t_meta_data_source where 1 = 1";
		if(StringUtils.isNotBlank(keyword)){
			countSql += " and (data_source_code like ? or data_source_name like ?)";
			paramList.add("%"+keyword+"%");
			paramList.add("%"+keyword+"%");
		}
		Object totalRecords = dmJdbcTemplate.queryForMap(countSql,paramList.toArray()).get("count");
		
		StringBuffer sql = new StringBuffer();
		sql.append("select id,concat(data_source_code ,'(',data_source_name,')') as match_content,");
		//sql.append("       '数据源' as match_name,");
		sql.append("       4 as match_type");
		sql.append("  from t_meta_data_source");
		sql.append(" where 1 = 1");
		if(StringUtils.isNotBlank(keyword)){
			sql.append(" and (data_source_code like ? or data_source_name like ?)");
		}
		sql.append(" order by oper_time desc limit 10");
		List<Map<String,Object>> datasourceList = dmJdbcTemplate.queryForList(sql.toString(),paramList.toArray());
		
		Map<String,Object> data = new HashMap<String,Object>();
		data.put("datasource_desc","数据源("+totalRecords+")");
		data.put("datasource_data", datasourceList);
		
		return data;
	}
	
	private Map<String,Object> getServicesSerachList(String keyword){
		List<Object> paramList = new ArrayList<Object>();
		
		String countSql = "select count(1) count from t_serv_api_base where 1 = 1";
		if(StringUtils.isNotBlank(keyword)){
			countSql += " and (api_name like ? or api_path like ?)";
			paramList.add("%"+keyword+"%");
			paramList.add("%"+keyword+"%");
		}
		Object totalRecords = dmJdbcTemplate.queryForMap(countSql,paramList.toArray()).get("count");
		
		StringBuffer sql = new StringBuffer();
		sql.append("select id,concat(api_name,'(',api_path,')') as match_content,");
		//sql.append("       '服务' as match_name,");
		sql.append("       5 as match_type");
		sql.append("  from t_serv_api_base");
		sql.append(" where 1 = 1");
		if(StringUtils.isNotBlank(keyword)){
			sql.append(" and (api_name like ? or api_path like ?)");
		}
		sql.append(" order by update_time desc limit 10");
		List<Map<String,Object>> servicesList = dmJdbcTemplate.queryForList(sql.toString(),paramList.toArray());
		
		Map<String,Object> data = new HashMap<String,Object>();
		data.put("services_desc","服务"+totalRecords+")");
		data.put("services_data", servicesList);
		
		return data;
	}
	
	private Map<String,Object> getProjectSerachList(String keyword){
		List<Object> paramList = new ArrayList<Object>();
		
		String countSql = "select count(1) count from t_meta_project where 1 = 1";
		if(StringUtils.isNotBlank(keyword)){
			countSql += " and (project_code like ? or project_name like ?)";
			paramList.add("%"+keyword+"%");
			paramList.add("%"+keyword+"%");
		}
		Object totalRecords = dmJdbcTemplate.queryForMap(countSql,paramList.toArray()).get("count");
		
		StringBuffer sql = new StringBuffer();
		sql.append("select id,concat(project_code,'(',project_name,')') as match_content,");
		//sql.append("       '项目' as match_name,");
		sql.append("       6 as match_type");
		sql.append("  from t_meta_project");
		sql.append(" where 1 = 1");
		if(StringUtils.isNotBlank(keyword)){
			sql.append(" and (project_code like ? or project_name like ?)");
		}
		sql.append(" order by create_time desc limit 10");
		List<Map<String,Object>> projectList = dmJdbcTemplate.queryForList(sql.toString(),paramList.toArray());
		
		Map<String,Object> data = new HashMap<String,Object>();
		data.put("project_desc","项目("+totalRecords+")");
		data.put("project_data", projectList);
		
		return data;
	}
	
	private Map<String,Object> getFuncSerachList(String keyword){
		List<Object> paramList = new ArrayList<Object>();
		
		String countSql = "select count(1) count from t_meta_func where 1 = 1";
		if(StringUtils.isNotBlank(keyword)){
			countSql += " and func_name like ?";
			paramList.add("%"+keyword+"%");
		}
		Object totalRecords = dmJdbcTemplate.queryForMap(countSql,paramList.toArray()).get("count");
		
		StringBuffer sql = new StringBuffer();
		sql.append("select id,concat(func_code,'(',func_name,')') as match_content,");
		//sql.append("       '函数' as match_name,");
		sql.append("       7 as match_type");
		sql.append("  from t_meta_func");
		sql.append(" where 1 = 1");
		if(StringUtils.isNotBlank(keyword)){
			sql.append(" and func_name like ?");
		}
		sql.append(" order by modify_time desc limit 10");
		List<Map<String,Object>> funcList = dmJdbcTemplate.queryForList(sql.toString(),paramList.toArray());
		
		Map<String,Object> data = new HashMap<String,Object>();
		data.put("func_desc","函数("+totalRecords+")");
		data.put("func_data", funcList);
		
		return data;
	}
}